/**
 * 数据库服务 - 负责与MySQL数据库通信
 */

// 使用数组而不是const，使得新添加的客户可以被添加到模拟数据中
let mockCustomerData = [
  {
    id: 1,
    name: '上海电子科技有限公司',
    address: '上海市浦东新区世纪大道1号',
    phone: '13812345678',
    contact_person: '张经理',
    business_type: '电子设备制造',
    location: '121.52,31.23',
    created_at: '2025-01-01 00:00:00'
  },
  {
    id: 2,
    name: '北京数据信息技术有限公司',
    address: '北京市海淀区中关村南大街5号',
    phone: '13987654321',
    contact_person: '李总',
    business_type: '信息技术服务',
    location: '116.32,39.99',
    created_at: '2025-01-02 00:00:00'
  },
  {
    id: 3,
    name: '广州智能系统科技有限公司',
    address: '广州市天河区珠江新城10号',
    phone: '13512345678',
    contact_person: '王工',
    business_type: '智能系统集成',
    location: '113.33,23.12',
    created_at: '2025-01-03 00:00:00'
  },
  {
    id: 4,
    name: '深圳新能源科技有限公司',
    address: '深圳市南山区科技园12号',
    phone: '13612345678',
    contact_person: '赵博士',
    business_type: '新能源技术',
    location: '114.06,22.54',
    created_at: '2025-01-04 00:00:00'
  },
  {
    id: 5,
    name: '杭州网络科技有限公司',
    address: '杭州市西湖区文三路478号',
    phone: '15812345678',
    contact_person: '钱总',
    business_type: '网络服务',
    location: '120.15,30.28',
    created_at: '2025-01-05 00:00:00'
  }
];

// 模拟数据访问记录
const mockVisitRecords = [
  {
    id: 1,
    customer_id: 1,
    visit_date: '2025-04-15 10:30:00',
    purpose: '产品演示',
    notes: '客户对我们的新产品非常感兴趣',
    feedback: '客户对价格有顾虑，需要考虑折扣方案',
    next_visit_date: '2025-05-05 14:00:00',
    created_at: '2025-04-15 10:30:00'
  },
  {
    id: 2,
    customer_id: 2,
    visit_date: '2025-04-16 14:00:00',
    purpose: '需求调研',
    notes: '讨论了客户的具体需求和预算',
    feedback: '客户需要一周时间内提供解决方案建议',
    next_visit_date: '2025-04-23 15:30:00',
    created_at: '2025-04-16 14:00:00'
  }
];

// 检查API服务是否可用
let apiAvailable = false;
const checkApiAvailability = async () => {
  try {
    const controller = new AbortController();
    const timeoutId = setTimeout(() => controller.abort(), 5000); // 增加超时时间
    
    const response = await fetch('http://localhost:3001/health-check', { 
      method: 'GET',
      headers: { 'Accept': 'application/json' },
      signal: controller.signal
    });
    
    clearTimeout(timeoutId);
    
    if (response.ok) {
      const data = await response.json();
      apiAvailable = data.status === 'ok';
      console.log('API服务检查结果:', data);
    } else {
      apiAvailable = false;
      console.warn('API服务返回非成功状态:', response.status);
    }
    
    return apiAvailable;
  } catch (error) {
    console.warn('API服务不可用，将使用模拟数据:', error);
    apiAvailable = false;
    return false;
  }
};

// 尝试检查API可用性，并定期重试
const initApiCheck = async () => {
  await checkApiAvailability(); // 初始检查
  console.log('初始API可用性检查结果:', apiAvailable);
  
  // 定期重新检查API可用性（每60秒）
  setInterval(async () => {
    const available = await checkApiAvailability();
    if (available && !apiAvailable) {
      console.log('API服务已恢复可用');
    } else if (!available && apiAvailable) {
      console.warn('API服务已变为不可用');
    }
    apiAvailable = available;
  }, 60000);
};

// 启动API检查
initApiCheck().catch(error => {
  console.error('API检查初始化失败:', error);
});

// 根据当前端口决定API请求地址
const getApiEndpoint = () => {
  return 'http://localhost:3001/api/db/query'; // 使用代理服务器地址
};

// MySQL查询函数
export async function executeQuery(sql: string, params?: any[]): Promise<any> {
  // 尝试连接数据库，不使用apiAvailable判断
  try {
    const apiEndpoint = getApiEndpoint();
    console.log(`发送请求到数据库: ${apiEndpoint}, SQL: ${sql}`);
    
    // 添加特殊头部，允许写操作
    const headers: Record<string, string> = {
      'Content-Type': 'application/json',
    };
    
    // 对于写操作，添加特殊头部
    if (sql.toLowerCase().includes('insert') || 
        sql.toLowerCase().includes('update') || 
        sql.toLowerCase().includes('delete')) {
      headers['x-allow-write'] = 'true';
    }
    
    const response = await fetch(apiEndpoint, {
      method: 'POST',
      headers,
      body: JSON.stringify({
        sql,
        params: params || []
      }),
    });

    // 如果数据库请求失败，抛出错误
    if (!response.ok) {
      const errorText = await response.text();
      throw new Error(`数据库请求失败: ${errorText}`);
    }

    const result = await response.json();
    return result;
  } catch (error) {
    console.error('数据库查询错误:', error);
    throw error; // 不使用模拟数据，而是抛出错误让调用者处理
  }
}

// 处理模拟查询
function handleMockQuery(sql: string, params?: any[]): any {
  console.log('模拟查询:', sql, params);
  
  // 基于SQL语句判断请求类型
  if (sql.toLowerCase().includes('select * from customers')) {
    return mockCustomerData;
  } 
  else if (sql.toLowerCase().includes('select * from customers where id =')) {
    const id = params?.[0];
    return [mockCustomerData.find(customer => customer.id.toString() === id)].filter(Boolean);
  }
  else if (sql.toLowerCase().includes('select * from visit_records')) {
    return mockVisitRecords;
  }
  else if (sql.toLowerCase().includes('insert into customers')) {
    // 模拟插入客户，并将客户添加到模拟数据中
    const newId = mockCustomerData.length > 0 ? Math.max(...mockCustomerData.map(c => Number(c.id))) + 1 : 1;
    
    // 从params中获取客户数据（正确匹配SQL语句的参数顺序）
    const newCustomer = {
      id: newId,
      name: params?.[0] || '未命名客户',
      address: params?.[1] || '未知地址',
      phone: params?.[2] || '未知电话',
      contact_person: params?.[3] || null,
      business_type: params?.[4] || null,
      location: params?.[5] || null,
      created_at: new Date().toISOString()
    };
    
    // 添加到模拟数据
    mockCustomerData.push(newCustomer);
    console.log('模拟数据 - 添加新客户:', newCustomer);
    
    // 返回结果
    return { insertId: newId, lastInsertId: newId };
  }
  else if (sql.toLowerCase().includes('update customers')) {
    // 模拟更新客户
    let id: string | number | null = null;
    if (params && params.length > 0) {
      // 最后一个参数通常是id
      id = params[params.length - 1];
    }
    
    if (id !== null) {
      // 找到要更新的客户
      const index = mockCustomerData.findIndex(c => c.id.toString() === id?.toString());
      if (index !== -1) {
        // 更新数据
        mockCustomerData[index] = {
          ...mockCustomerData[index],
          name: params?.[0] || mockCustomerData[index].name,
          address: params?.[1] || mockCustomerData[index].address,
          phone: params?.[2] || mockCustomerData[index].phone,
          contact_person: params?.[3],
          business_type: params?.[4],
          location: params?.[5]
        };
        console.log('模拟数据 - 更新客户:', mockCustomerData[index]);
      }
    }
    
    return { affectedRows: 1 };
  }
  else if (sql.toLowerCase().includes('delete from customers')) {
    // 模拟删除客户
    if (params && params.length > 0) {
      const id = params[0];
      const initialLength = mockCustomerData.length;
      mockCustomerData = mockCustomerData.filter(c => c.id.toString() !== id.toString());
      console.log(`模拟数据 - 删除客户ID: ${id}, 删除前: ${initialLength}, 删除后: ${mockCustomerData.length}`);
    }
    return { affectedRows: 1 };
  }
  
  // 默认返回空数组
  return [];
}

// 客户表操作
export const customerDb = {
  // 获取所有客户
  getAllCustomers: async () => {
    try {
      const result = await executeQuery('SELECT * FROM customers');
      return result.map((customer: any) => ({
        id: customer.id.toString(),
        name: customer.name,
        address: customer.address,
        phone: customer.phone,
        visitDuration: 60, // 默认拜访时长
        contact_person: customer.contact_person,
        business_type: customer.business_type,
        location: customer.location ? {
          longitude: parseFloat(customer.location.split(',')[0]),
          latitude: parseFloat(customer.location.split(',')[1])
        } : undefined
      }));
    } catch (error) {
      console.error('获取客户列表失败:', error);
      // 出错时不再使用模拟数据，而是返回空数组
      return [];
    }
  },

  // 获取单个客户
  getCustomerById: async (id: string) => {
    try {
      const results = await executeQuery('SELECT * FROM customers WHERE id = ?', [id]);
      if (results.length === 0) return null;
      
      const customer = results[0];
      return {
        id: customer.id.toString(),
        name: customer.name,
        address: customer.address,
        phone: customer.phone,
        visitDuration: 60, // 默认拜访时长
        contact_person: customer.contact_person,
        business_type: customer.business_type,
        location: customer.location ? {
          longitude: parseFloat(customer.location.split(',')[0]),
          latitude: parseFloat(customer.location.split(',')[1])
        } : undefined
      };
    } catch (error) {
      console.error('获取客户详情失败:', error);
      return null;
    }
  },

  // 添加客户
  addCustomer: async (customer: any) => {
    try {
      const locationStr = customer.location 
        ? `${customer.location.longitude},${customer.location.latitude}` 
        : null;
      
      const result = await executeQuery(
        `INSERT INTO customers (name, address, phone, contact_person, business_type, location) 
         VALUES (?, ?, ?, ?, ?, ?)`,
        [customer.name, customer.address, customer.phone, 
         customer.contact_person || null, customer.business_type || null, locationStr]
      );
      
      return {
        ...customer,
        id: result.insertId.toString()
      };
    } catch (error) {
      console.error('添加客户失败:', error);
      throw error;
    }
  },

  // 更新客户
  updateCustomer: async (customer: any) => {
    try {
      const locationStr = customer.location 
        ? `${customer.location.longitude},${customer.location.latitude}` 
        : null;
      
      await executeQuery(
        `UPDATE customers 
         SET name = ?, address = ?, phone = ?, contact_person = ?, business_type = ?, location = ? 
         WHERE id = ?`,
        [customer.name, customer.address, customer.phone, 
         customer.contact_person || null, customer.business_type || null, 
         locationStr, customer.id]
      );
      
      return customer;
    } catch (error) {
      console.error('更新客户失败:', error);
      throw error;
    }
  },

  // 删除客户
  deleteCustomer: async (id: string) => {
    try {
      await executeQuery('DELETE FROM customers WHERE id = ?', [id]);
      return true;
    } catch (error) {
      console.error('删除客户失败:', error);
      throw error;
    }
  }
};

// 访问记录表操作
export const visitRecordDb = {
  // 获取所有记录
  getAllVisitRecords: async () => {
    return await executeQuery(`
      SELECT vr.*, c.name as customer_name 
      FROM visit_records vr
      JOIN customers c ON vr.customer_id = c.id
      ORDER BY vr.visit_date DESC
    `);
  },

  // 获取客户的拜访记录
  getVisitRecordsByCustomerId: async (customerId: string) => {
    return await executeQuery(
      `SELECT * FROM visit_records 
       WHERE customer_id = ? 
       ORDER BY visit_date DESC`,
      [customerId]
    );
  },

  // 添加拜访记录
  addVisitRecord: async (record: any) => {
    const result = await executeQuery(
      `INSERT INTO visit_records 
       (customer_id, visit_date, purpose, notes, feedback, next_visit_date) 
       VALUES (?, ?, ?, ?, ?, ?)`,
      [record.customer_id, record.visit_date, record.purpose, 
       record.notes, record.feedback, record.next_visit_date]
    );
    
    return {
      ...record,
      id: result.lastInsertId
    };
  },

  // 更新拜访记录
  updateVisitRecord: async (record: any) => {
    await executeQuery(
      `UPDATE visit_records 
       SET customer_id = ?, visit_date = ?, purpose = ?, 
           notes = ?, feedback = ?, next_visit_date = ? 
       WHERE id = ?`,
      [record.customer_id, record.visit_date, record.purpose, 
       record.notes, record.feedback, record.next_visit_date, record.id]
    );
    
    return record;
  },

  // 删除拜访记录
  deleteVisitRecord: async (id: string) => {
    await executeQuery('DELETE FROM visit_records WHERE id = ?', [id]);
    return true;
  }
};

// 路线计划相关的数据库操作
export const routeDb = {
  // 将路线计划保存到本地存储
  saveRoutePlanToStorage: (plan: any) => {
    try {
      // 首先尝试清理localStorage中的旧数据
      try {
        // 获取所有localStorage键
        const allKeys = Object.keys(localStorage);
        // 如果键数量超过30，清理一些非必要数据
        if (allKeys.length > 30) {
          allKeys.forEach(key => {
            // 保留route_plans和其他重要数据
            if (key !== 'route_plans' && !key.includes('important')) {
              localStorage.removeItem(key);
            }
          });
        }
      } catch (e) {
        console.warn('清理localStorage其他数据失败:', e);
      }
      
      // 获取现有的路线计划
      const existingData = localStorage.getItem('route_plans');
      let plans = [];
      if (existingData) {
        try {
          plans = JSON.parse(existingData);
          if (!Array.isArray(plans)) {
            console.warn('路线计划数据格式不正确，重置为空数组');
            plans = [];
          }
        } catch (parseError) {
          console.warn('解析路线计划数据失败，重置为空数组:', parseError);
          plans = [];
        }
      }
      
      // 限制存储的路线计划数量，最多保留5条
      if (plans.length >= 5) {
        // 按创建时间排序，保留最新的
        plans.sort((a: any, b: any) => {
          const dateA = a.createdAt ? new Date(a.createdAt).getTime() : 0;
          const dateB = b.createdAt ? new Date(b.createdAt).getTime() : 0;
          return dateB - dateA; // 降序，最新的在前
        });
        // 只保留前4条，为新路线腾出空间
        plans = plans.slice(0, 4);
      }
      
      // 在计划中添加创建时间
      const planWithTimestamp = {
        ...plan,
        createdAt: plan.createdAt || new Date().toISOString()
      };
      
      // 检查是否已存在相同ID的计划
      const existingIndex = plans.findIndex((p: any) => p.id === plan.id);
      if (existingIndex >= 0) {
        // 如果存在，则更新
        plans[existingIndex] = planWithTimestamp;
      } else {
        // 如果不存在，则添加
        plans.push(planWithTimestamp);
      }
      
      try {
        // 尝试保存，但先删除一些可能导致存储过大的字段
        const safeToStorePlans = plans.map((p: any) => {
          // 创建一个新对象，不修改原对象
          const safeP = {...p};
          
          // 对复杂对象进行简化处理
          if (safeP.points && Array.isArray(safeP.points)) {
            safeP.points = safeP.points.map((point: any) => {
              const safePoint = {...point};
              // 移除可能很大的详细路线数据
              if (safePoint.routeDetails) {
                safePoint.routeDetails = null;
              }
              return safePoint;
            });
          }
          
          return safeP;
        });
        
        localStorage.setItem('route_plans', JSON.stringify(safeToStorePlans));
        return plan; // 返回原始完整的计划，不返回简化版
      } catch (storageError) {
        console.warn('localStorage空间不足，正在进行深度清理...');
        
        // 清除所有localStorage数据
        localStorage.clear();
        
        // 只保留当前路线，并进一步简化
        const simplifiedPlan = {
          ...planWithTimestamp,
          points: planWithTimestamp.points ? planWithTimestamp.points.map((p: any) => ({
            ...p,
            routeDetails: null, // 移除详细路线数据
            customer: {
              id: p.customer.id,
              name: p.customer.name,
              address: p.customer.address,
              location: p.customer.location
            } // 简化客户数据
          })) : []
        };
        
        try {
          localStorage.setItem('route_plans', JSON.stringify([simplifiedPlan]));
          return plan; // 返回原始完整的计划
        } catch (finalError) {
          console.error('即使清理后仍无法保存路线计划，本地存储可能已满:', finalError);
          localStorage.setItem('route_plans_error', 'Storage full');
          return plan;
        }
      }
    } catch (error) {
      console.error('保存路线计划到本地存储失败:', error);
      
      try {
        // 最终尝试：清空存储并保存极简版数据
        localStorage.clear();
        const minimalPlan = {
          id: plan.id,
          name: plan.name,
          totalDistance: plan.totalDistance,
          totalDuration: plan.totalDuration,
          createdAt: new Date().toISOString()
        };
        localStorage.setItem('route_plans', JSON.stringify([minimalPlan]));
      } catch (e) {
        console.error('无法保存路线计划，本地存储可能已损坏');
      }
      
      return plan;
    }
  },
  
  // 从本地存储获取所有路线计划
  getRoutePlansFromStorage: () => {
    try {
      const data = localStorage.getItem('route_plans');
      if (data) {
        return JSON.parse(data);
      }
      return [];
    } catch (error) {
      console.error('从本地存储获取路线计划失败:', error);
      return [];
    }
  },
  
  // 保存路线计划
  saveRoutePlan: async (plan: any) => {
    try {
      // 将路线计划保存到本地存储
      return routeDb.saveRoutePlanToStorage(plan);
    } catch (error) {
      console.error('保存路线计划失败:', error);
      throw error;
    }
  },
  
  // 获取所有路线计划
  getAllRoutePlans: async () => {
    try {
      // 从本地存储获取路线计划
      return routeDb.getRoutePlansFromStorage();
    } catch (error) {
      console.error('获取路线计划失败:', error);
      return [];
    }
  },
  
  // 获取路线计划详情
  getRoutePlanById: async (id: string) => {
    try {
      const plans = routeDb.getRoutePlansFromStorage();
      return plans.find((plan: any) => plan.id === id) || null;
    } catch (error) {
      console.error('获取路线计划详情失败:', error);
      return null;
    }
  },
  
  // 删除路线计划
  deleteRoutePlan: async (id: string) => {
    try {
      const plans = routeDb.getRoutePlansFromStorage();
      const newPlans = plans.filter((plan: any) => plan.id !== id);
      localStorage.setItem('route_plans', JSON.stringify(newPlans));
      return true;
    } catch (error) {
      console.error('删除路线计划失败:', error);
      return false;
    }
  }
}; 